﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using ZXCompany.Models.Dao;
using DBUtil;
using ZXCompany.Models.Entity;
namespace ZXCompany.Models.Service
{
    public class AboutService
    {
        DaoFactory daofac = new DaoFactory();
        Database db = new Database();
        /// <summary>
        /// 获取公司介绍
        /// </summary>
        /// <returns></returns>
        public ZX_CompanyInfo GetCompanyInfo()
        {
            SqlConnection sconn = db.GetConn();
            try
            {
                sconn.Open();
                return daofac.GetCInfoDao().Select(sconn);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        /// <summary>
        /// 获取经营理念
        /// </summary>
        /// <returns></returns>
        public ZX_RunIdea GetRunIdea()
        {
            SqlConnection sconn = db.GetConn();
            try
            {
                sconn.Open();
                return daofac.GetRunIdea().Select(sconn);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        #region 专家团队分类

        /// <summary>
        /// 获取专家种类
        /// </summary>
        /// <returns></returns>
        public List<ZX_ExpertType> GetExpertType()
        {
            SqlConnection sconn = db.GetConn();
            try
            {
                sconn.Open();
                List<ZX_ExpertType> lexpert = daofac.GetExpertTypeDao().Select(sconn);
                foreach (ZX_ExpertType ext in lexpert)
                {
                    ext.Lexpert = daofac.GetExpertInfoDao().SelectByTypeId(ext.ID, sconn);
                }
                return lexpert;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        /// <summary>
        /// 获取专家种类
        /// </summary>
        /// <returns></returns>
        public List<ZX_ExpertType> GetExpertTypeById(int etid)
        {
            SqlConnection sconn = db.GetConn();
            try
            {
                sconn.Open();
                List<ZX_ExpertType> lexpert = daofac.GetExpertTypeDao().SelectById( etid,sconn);
                foreach (ZX_ExpertType ext in lexpert)
                {
                    ext.Lexpert = daofac.GetExpertInfoDao().SelectByTypeId(ext.ID, sconn);
                }
                return lexpert;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }
        /// <summary>
        /// 获取专家种类
        /// </summary>
        /// <returns></returns>
        public void DeleteExpertTypeById(int etid)
        {
            SqlConnection sconn = db.GetConn();
            sconn.Open();
            SqlTransaction stran = sconn.BeginTransaction();
            try
            {
                daofac.GetExpertTypeDao().Delete(etid, sconn,stran);
                stran.Commit();
            }
            catch (Exception ex)
            {
                stran.Rollback();
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }


        /// <summary>
        /// 获取专家种类
        /// </summary>
        /// <returns></returns>
        public int GetExpertTypeMaxId()
        {
            SqlConnection sconn = db.GetConn();
            try
            {
                sconn.Open();
                return daofac.GetExpertTypeDao().SelectMaxId(sconn);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        /// <summary>
        /// 获取专家种类
        /// </summary>
        /// <returns></returns>
        public void UpdateExpertType(ZX_ExpertType zxenty)
        {
            SqlConnection sconn = db.GetConn();
            sconn.Open();
            SqlTransaction stran = sconn.BeginTransaction();
            try
            {
                daofac.GetExpertTypeDao().Update(zxenty, sconn, stran);
                stran.Commit();
            }
            catch (Exception ex)
            {
                stran.Rollback();
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        /// <summary>
        /// 获取专家种类
        /// </summary>
        /// <returns></returns>
        public void InsertExpertType(ZX_ExpertType zxenty)
        {
            SqlConnection sconn = db.GetConn();
            sconn.Open();
            SqlTransaction stran = sconn.BeginTransaction();
            try
            {
                daofac.GetExpertTypeDao().Insert(zxenty, sconn, stran);
                stran.Commit();
            }
            catch (Exception ex)
            {
                stran.Rollback();
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        #endregion

        #region 获取专家信息

        /// <summary>
        /// 获取专家信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<ZX_ExpertInfo> GetExpertInfo()
        {
            SqlConnection sconn = db.GetConn();
            try
            {
                sconn.Open();
                return daofac.GetExpertInfoDao().Select(sconn);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        /// <summary>
        /// 获取专家信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ZX_ExpertInfo GetExpertInfoById(int id)
        {
            SqlConnection sconn = db.GetConn();
            try
            {
                sconn.Open();
                ZX_ExpertInfo extinfo = null;
                if ((daofac.GetExpertInfoDao().SelectById(id,sconn)).Count > 0)
                {
                    extinfo = daofac.GetExpertInfoDao().SelectById(id, sconn)[0];
                }
               
                return extinfo;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        /// <summary>
        /// 删除专家种类
        /// </summary>
        /// <returns></returns>
        public void DeleteExpertInfoById(int etid)
        {
            SqlConnection sconn = db.GetConn();
            sconn.Open();
            SqlTransaction stran = sconn.BeginTransaction();
            try
            {
                daofac.GetExpertInfoDao().Delete(etid, sconn, stran);
                stran.Commit();
            }
            catch (Exception ex)
            {
                stran.Rollback();
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        /// <summary>
        /// 更新专家种类
        /// </summary>
        /// <returns></returns>
        public void UpdateExpertInfo(ZX_ExpertInfo zxenty)
        {
            SqlConnection sconn = db.GetConn();
            sconn.Open();
            SqlTransaction stran = sconn.BeginTransaction();
            try
            {
                daofac.GetExpertInfoDao().Update(zxenty, sconn, stran);
                stran.Commit();
            }
            catch (Exception ex)
            {
                stran.Rollback();
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        /// <summary>
        /// 插入专家种类
        /// </summary>
        /// <returns></returns>
        public void InsertExpertInfo(ZX_ExpertInfo zxenty)
        {
            SqlConnection sconn = db.GetConn();
            sconn.Open();
            SqlTransaction stran = sconn.BeginTransaction();
            try
            {
                daofac.GetExpertInfoDao().Insert(zxenty, sconn, stran);
                stran.Commit();
            }
            catch (Exception ex)
            {
                stran.Rollback();
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        #endregion

        #region 培训系列图片列表
        /// <summary>
        /// 获取培训系列图片列表
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public List<ZX_TrainList> GetTrainList()
        {
            SqlConnection sconn = db.GetConn();
            try
            {
                sconn.Open();
                return daofac.GetTrainListDao().Select(sconn);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        /// <summary>
        /// 获取培训系列图片列表
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ZX_TrainList GetTrainListById(int id)
        {
            SqlConnection sconn = db.GetConn();
            try
            {
                sconn.Open();
                ZX_TrainList extinfo = daofac.GetTrainListDao().SelectByID(id,sconn);               
                return extinfo;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        /// <summary>
        /// 删除培训系列图片列表
        /// </summary>
        /// <returns></returns>
        public void DeleteTrainListById(int etid)
        {
            SqlConnection sconn = db.GetConn();
            sconn.Open();
            SqlTransaction stran = sconn.BeginTransaction();
            try
            {
                daofac.GetTrainListDao().Delete(etid, sconn, stran);
                stran.Commit();
            }
            catch (Exception ex)
            {
                stran.Rollback();
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        /// <summary>
        /// 更新培训系列图片列表
        /// </summary>
        /// <returns></returns>
        public void UpdateTrainList(ZX_TrainList zxenty)
        {
            SqlConnection sconn = db.GetConn();
            sconn.Open();
            SqlTransaction stran = sconn.BeginTransaction();
            try
            {
                daofac.GetTrainListDao().Update(zxenty, sconn, stran);
                stran.Commit();
            }
            catch (Exception ex)
            {
                stran.Rollback();
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        /// <summary>
        /// 插入培训系列图片列表
        /// </summary>
        /// <returns></returns>
        public void InsertTrainList(ZX_TrainList zxenty)
        {
            SqlConnection sconn = db.GetConn();
            sconn.Open();
            SqlTransaction stran = sconn.BeginTransaction();
            try
            {
                daofac.GetTrainListDao().Insert(zxenty, sconn, stran);
                stran.Commit();
            }
            catch (Exception ex)
            {
                stran.Rollback();
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        #endregion

        #region 通用方法

        /// <summary>
        /// 获取对应表里边的对应字段的最大值+1
        /// </summary>
        /// <returns></returns>
        public int GetExpertTypeMaxId(string tabstr,string fieldstr)
        {
            SqlConnection sconn = db.GetConn();
            try
            {
                sconn.Open();
                return daofac.GetExpertTypeDao().SelectMaxId(tabstr,fieldstr,sconn);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }
        #endregion

        #region 公司观点

        /// <summary>
        /// 获取公司观点
        /// </summary>
        /// <returns></returns>
        public List<ZX_CompanyPoint> GetCompanyPoint()
        {
            SqlConnection sconn = db.GetConn();
            try
            {
                sconn.Open();
                List<ZX_CompanyPoint> lexpert = daofac.GetCompanyPointDao().Select(sconn);
                return lexpert;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        /// <summary>
        /// 获取公司观点
        /// </summary>
        /// <returns></returns>
        public List<ZX_CompanyPoint> GetCompanyPointById(int etid)
        {
            SqlConnection sconn = db.GetConn();
            try
            {
                sconn.Open();
                List<ZX_CompanyPoint> lexpert = daofac.GetCompanyPointDao().SelectById(etid, sconn);
                return lexpert;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }
        /// <summary>
        /// 获取公司观点
        /// </summary>
        /// <returns></returns>
        public void DeleteCompanyPointById(int etid)
        {
            SqlConnection sconn = db.GetConn();
            sconn.Open();
            SqlTransaction stran = sconn.BeginTransaction();
            try
            {
                daofac.GetCompanyPointDao().Delete(etid, sconn, stran);
                stran.Commit();
            }
            catch (Exception ex)
            {
                stran.Rollback();
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }


        /// <summary>
        /// 获取公司观点
        /// </summary>
        /// <returns></returns>
        public int GetCompanyPointMaxId()
        {
            SqlConnection sconn = db.GetConn();
            try
            {
                sconn.Open();
                return daofac.GetCompanyPointDao().SelectMaxId(sconn);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        /// <summary>
        /// 获取公司观点
        /// </summary>
        /// <returns></returns>
        public void UpdateCompanyPoint(ZX_CompanyPoint zxenty)
        {
            SqlConnection sconn = db.GetConn();
            sconn.Open();
            SqlTransaction stran = sconn.BeginTransaction();
            try
            {
                daofac.GetCompanyPointDao().Update(zxenty, sconn, stran);
                stran.Commit();
            }
            catch (Exception ex)
            {
                stran.Rollback();
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        /// <summary>
        /// 获取公司观点
        /// </summary>
        /// <returns></returns>
        public void InsertCompanyPoint(ZX_CompanyPoint zxenty)
        {
            SqlConnection sconn = db.GetConn();
            sconn.Open();
            SqlTransaction stran = sconn.BeginTransaction();
            try
            {
                daofac.GetCompanyPointDao().Insert(zxenty, sconn, stran);
                stran.Commit();
            }
            catch (Exception ex)
            {
                stran.Rollback();
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        #endregion

        #region 在线留言
        /// <summary>
        /// 获取在线留言
        /// </summary>
        /// <returns></returns>
        public List<ZX_LeaveMassage> GetLeaveMassage()
        {
            SqlConnection sconn = db.GetConn();
            try
            {
                sconn.Open();
                List<ZX_LeaveMassage> lexpert = daofac.GetLeaveMassageDao().Select(sconn);
                return lexpert;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        /// <summary>
        /// 获取在线留言
        /// </summary>
        /// <returns></returns>
        public List<ZX_LeaveMassage> GetLeaveMassageById(int etid)
        {
            SqlConnection sconn = db.GetConn();
            try
            {
                sconn.Open();
                List<ZX_LeaveMassage> lexpert = daofac.GetLeaveMassageDao().SelectById(etid, sconn);
                return lexpert;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }
        /// <summary>
        /// 删除在线留言
        /// </summary>
        /// <returns></returns>
        public void DeleteLeaveMassageById(int etid)
        {
            SqlConnection sconn = db.GetConn();
            sconn.Open();
            SqlTransaction stran = sconn.BeginTransaction();
            try
            {
                daofac.GetLeaveMassageDao().Delete(etid, sconn, stran);
                stran.Commit();
            }
            catch (Exception ex)
            {
                stran.Rollback();
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        /// <summary>
        /// 更新在线留言
        /// </summary>
        /// <returns></returns>
        public void UpdateLeaveMassage(ZX_LeaveMassage zxenty)
        {
            SqlConnection sconn = db.GetConn();
            sconn.Open();
            SqlTransaction stran = sconn.BeginTransaction();
            try
            {
                daofac.GetLeaveMassageDao().Update(zxenty, sconn, stran);
                stran.Commit();
            }
            catch (Exception ex)
            {
                stran.Rollback();
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        /// <summary>
        /// 插入在线留言
        /// </summary>
        /// <returns></returns>
        public void InsertLeaveMassage(ZX_LeaveMassage zxenty)
        {
            SqlConnection sconn = db.GetConn();
            sconn.Open();
            SqlTransaction stran = sconn.BeginTransaction();
            try
            {
                daofac.GetLeaveMassageDao().Insert(zxenty, sconn, stran);
                stran.Commit();
            }
            catch (Exception ex)
            {
                stran.Rollback();
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        #endregion

        #region 公司新闻

        /// <summary>
        /// 获取公司新闻
        /// </summary>
        /// <returns></returns>
        public List<ZX_News> GetNews()
        {
            SqlConnection sconn = db.GetConn();
            try
            {
                sconn.Open();
                List<ZX_News> lexpert = daofac.GetNewsDao().Select(sconn);
                return lexpert;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        /// <summary>
        /// 获取公司新闻
        /// </summary>
        /// <returns></returns>
        public List<ZX_News> GetNewsById(int etid)
        {
            SqlConnection sconn = db.GetConn();
            try
            {
                sconn.Open();
                List<ZX_News> lexpert = daofac.GetNewsDao().SelectById(etid, sconn);
                return lexpert;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }
        /// <summary>
        /// 删除公司新闻
        /// </summary>
        /// <returns></returns>
        public void DeleteNewsById(int etid)
        {
            SqlConnection sconn = db.GetConn();
            sconn.Open();
            SqlTransaction stran = sconn.BeginTransaction();
            try
            {
                daofac.GetNewsDao().Delete(etid, sconn, stran);
                stran.Commit();
            }
            catch (Exception ex)
            {
                stran.Rollback();
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        /// <summary>
        /// 更新公司新闻
        /// </summary>
        /// <returns></returns>
        public void UpdateNews(ZX_News zxenty)
        {
            SqlConnection sconn = db.GetConn();
            sconn.Open();
            SqlTransaction stran = sconn.BeginTransaction();
            try
            {
                daofac.GetNewsDao().Update(zxenty, sconn, stran);
                stran.Commit();
            }
            catch (Exception ex)
            {
                stran.Rollback();
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        /// <summary>
        /// 插入公司新闻
        /// </summary>
        /// <returns></returns>
        public void InsertNews(ZX_News zxenty)
        {
            SqlConnection sconn = db.GetConn();
            sconn.Open();
            SqlTransaction stran = sconn.BeginTransaction();
            try
            {
                daofac.GetNewsDao().Insert(zxenty, sconn, stran);
                stran.Commit();
            }
            catch (Exception ex)
            {
                stran.Rollback();
                throw ex;
            }
            finally
            {
                db.CloseConnection(sconn);
            }
        }

        #endregion
    }
}